Analyzing Companies Performance

This project represents our inaugural analysis of four companies within the commercial sector over a three-year period, from 2021 to 2023, segmented by quarters. The database utilized in this analysis is entirely hypothetical and does not reflect actual figures or pertain to any real-world company.

Through comprehensive data analysis, we have extracted key performance indicators (KPIs) for each of the four companies. Based on these KPIs, we have formulated conclusions and developed strategic recommendations for our organization.

Scenario: I am a financial analyst at Tech Company, which operates in a commercial market comprising four companies. Our company has been experiencing a decline in both product sales and profits over time, unlike our competitors who are enjoying high profitability.

->NOTE: If you have any confusing just read Print Function in the at the Code it will be highly enough.

Below is the original hypothetical database used in this analysis:

library(opencpu)
## Loading config from C:/Users/A.Dawod/AppData/Local/R/win-library/4.4/opencpu/config/defaults.conf
## Loading config from C:\Users\A.Dawod\AppData\Roaming/R/config/R/opencpu/user.conf
## Welcome to OpenCPU!
library(readxl)
library(dplyr)
## 
## Attaching package: 'dplyr'
## The following objects are masked from 'package:stats':
## 
##     filter, lag
## The following objects are masked from 'package:base':
## 
##     intersect, setdiff, setequal, union
library(tidyr)
options(tibble.print_max = 1000)
income_statment <- read_excel("D:/Project/Project_Four_Companies(Orginal).xlsx", sheet = 1)
balance_sheet_Assets <- read_excel("D:/Project/Project_Four_Companies(Orginal).xlsx", sheet = 2)
balance_sheet_L_S <- read_excel("D:/Project/Project_Four_Companies(Orginal).xlsx", sheet = 3)
excel_data<-bind_rows(income_statment,balance_sheet_Assets,balance_sheet_L_S)
### Display the data
print(income_statment)
## # A tibble: 48 × 12
##    Company Quarter  Year Revenue    COGS Gross_Profit Operating_Expenses    EBIT
##    <chr>   <chr>   <dbl>   <dbl>   <dbl>        <dbl>              <dbl>   <dbl>
##  1 Tech    Q1       2021 648721. 365391.      283331.            189537.  93793.
##  2 Tech    Q2       2021 565250. 260978.      304271.            127755. 176516.
##  3 Tech    Q3       2021 758622. 454160.      304462.            172230. 132231.
##  4 Tech    Q4       2021 669067. 350268.      318798.            143990. 174809.
##  5 Tech    Q1       2022 881818. 429210.      452608.            256685. 195924.
##  6 Tech    Q2       2022 518744. 278455.      240289.            121972. 118317.
##  7 Tech    Q3       2022 656602. 344829.      311773.            133764. 178009.
##  8 Tech    Q4       2022 922800. 489665.      433135.            250773. 182361.
##  9 Tech    Q1       2023 639024. 348730.      290295.            178966. 111329.
## 10 Tech    Q2       2023 595526. 336003.      259523.            150406. 109117.
## 11 Tech    Q3       2023 869983. 390271.      479712.            212269. 267442.
## 12 Tech    Q4       2023 731067. 436447.      294620.            212877.  81743.
## 13 Speed   Q1       2021 657220. 333620.      323600.            186260. 137340.
## 14 Speed   Q2       2021 980669. 576060.      404609.            241333. 163276.
## 15 Speed   Q3       2021 551447. 314556.      236891.            163765.  73126.
## 16 Speed   Q4       2021 787047. 347393.      439655.            234290. 205365.
## 17 Speed   Q1       2022 641032. 308822.      332209.            171591. 160618.
## 18 Speed   Q2       2022 563315. 310093.      253222.            156534.  96688.
## 19 Speed   Q3       2022 994683. 502147.      492537.            286637. 205899.
## 20 Speed   Q4       2022 658016. 344925.      313091.            142805. 170286.
## 21 Speed   Q1       2023 574977. 330688.      244288.            132368. 111921.
## 22 Speed   Q2       2023 677225. 282789.      394436.            139115. 255320.
## 23 Speed   Q3       2023 900921. 386850.      514072.            216327. 297745.
## 24 Speed   Q4       2023 520673. 230877.      289795.            117837. 171958.
## 25 Maclr   Q1       2021 575230. 309856.      265373.            155277. 110097.
## 26 Maclr   Q2       2021 588116. 331783.      256333.            134266. 122067.
## 27 Maclr   Q3       2021 830948. 495346.      335602.            231040. 104562.
## 28 Maclr   Q4       2021 610934. 327718.      283216.            174450. 108765.
## 29 Maclr   Q1       2022 621120. 297236.      323884.            173750. 150134.
## 30 Maclr   Q2       2022 844889. 459040.      385850.            226940. 158910.
## 31 Maclr   Q3       2022 573364. 306862.      266502.            153783. 112719.
## 32 Maclr   Q4       2022 849582. 352117.      497464.            218594. 278871.
## 33 Maclr   Q1       2023 810913. 403150.      407763.            194734. 213029.
## 34 Maclr   Q2       2023 634501. 256295.      378206.            137244. 240962.
## 35 Maclr   Q3       2023 525940. 290613.      235327.            148695.  86632.
## 36 Maclr   Q4       2023 723581. 348445.      375136.            157471. 217665.
## 37 Dain    Q1       2021 582244. 316333.      265911.            174002.  91909.
## 38 Dain    Q2       2021 600186. 334554.      265632.            122685. 142947.
## 39 Dain    Q3       2021 950352. 556525.      393827.            272291. 121536.
## 40 Dain    Q4       2021 970666. 471660.      499005.            243956. 255049.
## 41 Dain    Q1       2022 512620. 214573.      298047.            145761. 152286.
## 42 Dain    Q2       2022 562047. 268560.      293487.            136949. 156538.
## 43 Dain    Q3       2022 690394. 402282.      288113.            147043. 141069.
## 44 Dain    Q4       2022 892523. 534628.      357894.            209652. 148242.
## 45 Dain    Q1       2023 786445. 346214.      440230.            219762. 220468.
## 46 Dain    Q2       2023 916610. 455940.      460670.            195881. 264789.
## 47 Dain    Q3       2023 744888. 362262.      382626.            166597. 216030.
## 48 Dain    Q4       2023 883215. 404156.      479059.            225678. 253381.
## # ℹ 4 more variables: Interest_Expense <dbl>, EBT <dbl>, Tax_Expense <dbl>,
## #   Net_Income <dbl>
print(balance_sheet_Assets)
## # A tibble: 48 × 9
##    Company Quarter  Year    Cash Accounts_Receivable Inventory Current_Assets
##    <chr>   <chr>   <dbl>   <dbl>               <dbl>     <dbl>          <dbl>
##  1 Tech    Q1       2021 108280.              67301.    60333.        235914.
##  2 Tech    Q2       2021  74653.              57080.    36565.        168298.
##  3 Tech    Q3       2021  59313.              84926.    59168.        203407.
##  4 Tech    Q4       2021 121790.             108084.    66411.        296284.
##  5 Tech    Q1       2022  55973.             113650.    48630.        218253.
##  6 Tech    Q2       2022  80702.             102817.    33238.        216757.
##  7 Tech    Q3       2022  88388.             127710.    61840.        277938.
##  8 Tech    Q4       2022  67934.             129025.    86907.        283866.
##  9 Tech    Q1       2023  72952.              67319.    69029.        209300.
## 10 Tech    Q2       2023 132511.              92360.    46537.        271407.
## 11 Tech    Q3       2023 114974.             163541.    42600.        321115.
## 12 Tech    Q4       2023  54695.              81079.    84751.        220525.
## 13 Speed   Q1       2021 114201.             124978.    56891.        296070.
## 14 Speed   Q2       2021  55393.             170537.   101008.        326938.
## 15 Speed   Q3       2021 117534.              80479.    47351.        245365.
## 16 Speed   Q4       2021  63231.             114318.    53936.        231486.
## 17 Speed   Q1       2022  90856.              64648.    51327.        206832.
## 18 Speed   Q2       2022 127823.              80929.    50100.        258853.
## 19 Speed   Q3       2022  92629.             128247.    65873.        286749.
## 20 Speed   Q4       2022  93628.             123767.    41367.        258762.
## 21 Speed   Q1       2023 109554.              91860.    52643.        254056.
## 22 Speed   Q2       2023  56288.             106295.    38850.        201433.
## 23 Speed   Q3       2023  55336.             108447.    51040.        214822.
## 24 Speed   Q4       2023 110499.              77886.    25332.        213716.
## 25 Maclr   Q1       2021 114330.              61252.    44240.        219822.
## 26 Maclr   Q2       2021  52048.             108249.    66052.        226348.
## 27 Maclr   Q3       2021  82547.             112199.    95321.        290067.
## 28 Maclr   Q4       2021  88720.              97200.    49054.        234974.
## 29 Maclr   Q1       2022  60878.             114294.    34612.        209784.
## 30 Maclr   Q2       2022  76411.             129880.    50996.        257286.
## 31 Maclr   Q3       2022 121766.              71789.    60031.        253586.
## 32 Maclr   Q4       2022  57241.             143620.    48755.        249616.
## 33 Maclr   Q1       2023 144067.              86458.    58828.        289354.
## 34 Maclr   Q2       2023  66585.              66363.    49833.        182781.
## 35 Maclr   Q3       2023  58025.              89493.    32418.        179936.
## 36 Maclr   Q4       2023  94109.             120419.    41572.        256100.
## 37 Dain    Q1       2021  73285.              79141.    62189.        214615.
## 38 Dain    Q2       2021 118274.             104071.    53933.        276278.
## 39 Dain    Q3       2021  81358.             141023.   101902.        324283.
## 40 Dain    Q4       2021  68008.             129744.    84692.        282444.
## 41 Dain    Q1       2022 109980.              71892.    33210.        215083.
## 42 Dain    Q2       2022  86083.              91393.    37635.        215110.
## 43 Dain    Q3       2022 128536.              87550.    66957.        283043.
## 44 Dain    Q4       2022 104447.             105628.    73960.        284034.
## 45 Dain    Q1       2023  77457.             143409.    38354.        259219.
## 46 Dain    Q2       2023  55170.             139965.    46134.        241270.
## 47 Dain    Q3       2023  65167.             127152.    42307.        234625.
## 48 Dain    Q4       2023 136867.             153258.    62151.        352276.
## # ℹ 2 more variables: Property_Plant_Equipment <dbl>, Total_Assets <dbl>
print(balance_sheet_L_S)
## # A tibble: 48 × 12
##    Company Quarter  Year Accounts_Payable Short_Term_Debt Current_Liabilities
##    <chr>   <chr>   <dbl>            <dbl>           <dbl>               <dbl>
##  1 Tech    Q1       2021           64310.          82956.             147266.
##  2 Tech    Q2       2021           34109.          64510.              98619.
##  3 Tech    Q3       2021           52609.         114660.             167269.
##  4 Tech    Q4       2021           47387.         107439.             154827.
##  5 Tech    Q1       2022           47422.         130525.             177948.
##  6 Tech    Q2       2022           51469.          70189.             121658.
##  7 Tech    Q3       2022           68962.          93160.             162122.
##  8 Tech    Q4       2022           92628.          98568.             191196.
##  9 Tech    Q1       2023           56606.          90380.             146987.
## 10 Tech    Q2       2023           43997.          82808.             126805.
## 11 Tech    Q3       2023           62431.         128085.             190516.
## 12 Tech    Q4       2023           64435.         125726.             190161.
## 13 Speed   Q1       2021           64772.          74729.             139501.
## 14 Speed   Q2       2021           78770.         192558.             271328.
## 15 Speed   Q3       2021           46572.          84371.             130943.
## 16 Speed   Q4       2021           62834.          92974.             155809.
## 17 Speed   Q1       2022           38498.         119104.             157602.
## 18 Speed   Q2       2022           43219.          98778.             141997.
## 19 Speed   Q3       2022           94898.         192936.             287834.
## 20 Speed   Q4       2022           49201.          85096.             134297.
## 21 Speed   Q1       2023           58343.          77501.             135844.
## 22 Speed   Q2       2023           44772.         100302.             145075.
## 23 Speed   Q3       2023           62281.         121023.             183304.
## 24 Speed   Q4       2023           28680.          97510.             126190.
## 25 Maclr   Q1       2021           34330.          58318.              92649.
## 26 Maclr   Q2       2021           42400.          97854.             140253.
## 27 Maclr   Q3       2021           65110.         107651.             172761.
## 28 Maclr   Q4       2021           58549.         121313.             179862.
## 29 Maclr   Q1       2022           50651.         119400.             170050.
## 30 Maclr   Q2       2022           85335.          88028.             173363.
## 31 Maclr   Q3       2022           40485.          65364.             105849.
## 32 Maclr   Q4       2022           69875.          87218.             157093.
## 33 Maclr   Q1       2023           59586.         109525.             169111.
## 34 Maclr   Q2       2023           41490.         122399.             163888.
## 35 Maclr   Q3       2023           47917.          98455.             146372.
## 36 Maclr   Q4       2023           63729.         134469.             198198.
## 37 Dain    Q1       2021           57002.         113184.             170186.
## 38 Dain    Q2       2021           39148.          65784.             104933.
## 39 Dain    Q3       2021           85216.         113397.             198613.
## 40 Dain    Q4       2021           50292.         106791.             157082.
## 41 Dain    Q1       2022           28161.         102249.             130410.
## 42 Dain    Q2       2022           27863.          96623.             124485.
## 43 Dain    Q3       2022           43686.          82917.             126602.
## 44 Dain    Q4       2022           91711.         173419.             265130.
## 45 Dain    Q1       2023           64621.          85810.             150432.
## 46 Dain    Q2       2023           76721.         160293.             237014.
## 47 Dain    Q3       2023           44534.         144499.             189033.
## 48 Dain    Q4       2023           44532.         149466.             193998.
## # ℹ 6 more variables: Long_Term_Debt <dbl>, Total_Liabilities <dbl>,
## #   Common_Stock <dbl>, Retained_Earnings <dbl>, Shareholders_Equity <dbl>,
## #   Total_Liabilities_and_Equity <dbl>

Subsequently, I transformed the original database to enhance its readability and clarity. This transformation includes organizing the data in a structured format, making it easier to interpret and analyze. Here’s how the data now looks:

 Tech <- read_excel("D:/Project/P_Adjusted2.xlsx", sheet = 1)
## New names:
## • `` -> `...2`
## • `` -> `...3`
## • `` -> `...4`
## • `` -> `...5`
## • `` -> `...6`
## • `` -> `...7`
## • `` -> `...8`
## • `` -> `...9`
## • `` -> `...10`
## • `` -> `...11`
## • `` -> `...12`
## • `` -> `...13`
 Speed <- read_excel("D:/Project/P_Adjusted2.xlsx", sheet = 2)
## New names:
## • `` -> `...2`
## • `` -> `...3`
## • `` -> `...4`
## • `` -> `...5`
## • `` -> `...6`
## • `` -> `...7`
## • `` -> `...8`
## • `` -> `...9`
## • `` -> `...10`
## • `` -> `...11`
## • `` -> `...12`
## • `` -> `...13`
 Maclr <- read_excel("D:/Project/P_Adjusted2.xlsx", sheet = 3)
## New names:
## • `` -> `...2`
## • `` -> `...3`
## • `` -> `...4`
## • `` -> `...5`
## • `` -> `...6`
## • `` -> `...7`
## • `` -> `...8`
## • `` -> `...9`
## • `` -> `...10`
## • `` -> `...11`
## • `` -> `...12`
## • `` -> `...13`
 Dain <- read_excel("D:/Project/P_Adjusted2.xlsx", sheet = 4)
## New names:
## • `` -> `...2`
## • `` -> `...3`
## • `` -> `...4`
## • `` -> `...5`
## • `` -> `...6`
## • `` -> `...7`
## • `` -> `...8`
## • `` -> `...9`
## • `` -> `...10`
## • `` -> `...11`
## • `` -> `...12`
## • `` -> `...13`
print(Tech)
## # A tibble: 33 × 13
##    `Income Statement`      ...2  ...3  ...4  ...5  ...6  ...7  ...8  ...9  ...10
##    <chr>                   <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr>
##  1 <NA>                    2021  <NA>  <NA>  <NA>  2022  <NA>  <NA>  <NA>  2023 
##  2 Quarter                 Q1    Q2    Q3    Q4    Q1    Q2    Q3    Q4    Q1   
##  3 Revenue                 6487… 5652… 7586… 6690… 8818… 5187… 6566… 9228… 6390…
##  4 COGS                    3653… 2609… 4541… 3502… 4292… 2784… 3448… 4896… 3487…
##  5 Gross_Profit            2833… 3042… 3044… 3187… 4526… 2402… 3117… 4331… 2902…
##  6 Operating_Expenses      1895… 1277… 1722… 1439… 2566… 1219… 1337… 2507… 1789…
##  7 EBIT                    93793 1765… 1322… 1748… 1959… 1183… 1780… 1823… 1113…
##  8 Interest_Expense        1851  4469  11493 3853  5038  8259  9580  6491  4776 
##  9 EBT                     91943 1720… 1207… 1709… 1908… 1100… 1684… 1758… 1065…
## 10 Tax_Expense             27583 51614 36222 51287 57266 33017 50529 52761 31966
## 11 Net_Income              64360 1204… 84517 1196… 1336… 77041 1179… 1231… 74587
## 12 Balance Sheet           <NA>  <NA>  <NA>  <NA>  <NA>  <NA>  <NA>  <NA>  <NA> 
## 13 Assets                  <NA>  <NA>  <NA>  <NA>  <NA>  <NA>  <NA>  <NA>  <NA> 
## 14 <NA>                    2021  <NA>  <NA>  <NA>  2022  <NA>  <NA>  <NA>  2023 
## 15 Quarter                 Q1    Q2    Q3    Q4    Q1    Q2    Q3    Q4    Q1   
## 16 Cash                    1082… 74653 59313 1217… 55973 80702 88388 67934 72952
## 17 Accounts_Receivable     67301 57080 84926 1080… 1136… 1028… 1277… 1290… 67319
## 18 Inventory               60333 36565 59168 66411 48630 33238 61840 86907 69029
## 19 Current_Assets          2359… 1682… 2034… 2962… 2182… 2167… 2779… 2838… 2093…
## 20 Property_Plant_Equipme… 4434… 5110… 7229… 4294… 6787… 3160… 6128… 6418… 5527…
## 21 Total_Assets            6793… 6793… 9263… 7257… 8969… 5328… 8907… 9256… 7620…
## 22 Liability & Shareholde… <NA>  <NA>  <NA>  <NA>  <NA>  <NA>  <NA>  <NA>  <NA> 
## 23 <NA>                    2021  <NA>  <NA>  <NA>  2022  <NA>  <NA>  <NA>  2023 
## 24 Quarter                 Q1    Q2    Q3    Q4    Q1    Q2    Q3    Q4    Q1   
## 25 Accounts_Payable        64310 34109 52609 47387 47422 51469 68962 92628 56606
## 26 Short_Term_Debt         82956 64510 1146… 1074… 1305… 70189 93160 98568 90380
## 27 Current_Liabilities     1472… 98619 1672… 1548… 1779… 1216… 1621… 1911… 1469…
## 28 Long_Term_Debt          1608… 1272… 1625… 2018… 2091… 2021… 2023… 2848… 2510…
## 29 Total_Liabilities       3081… 2258… 3297… 3567… 3871… 3237… 3644… 4760… 3980…
## 30 Common_Stock            71442 79474 1718… 1067… 1773… 79501 90511 1284… 1259…
## 31 Retained_Earnings       2998… 3740… 4247… 2622… 3324… 1295… 4358… 3211… 2380…
## 32 Shareholders_Equity     3712… 4535… 5966… 3690… 5098… 2090… 5263… 4496… 3640…
## 33 Total_Liabilities_and_… 6793… 6793… 9263… 7257… 8969… 5328… 8907… 9256… 7620…
## # ℹ 3 more variables: ...11 <chr>, ...12 <chr>, ...13 <chr>
print(Speed)
## # A tibble: 33 × 13
##    `Income Statement`      ...2  ...3  ...4  ...5  ...6  ...7  ...8  ...9  ...10
##    <chr>                   <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr>
##  1 <NA>                    2021  <NA>  <NA>  <NA>  2022  <NA>  <NA>  <NA>  2023 
##  2 Quarter                 Q1    Q2    Q3    Q4    Q1    Q2    Q3    Q4    Q1   
##  3 Revenue                 6572… 9806… 5514… 7870… 6410… 5633… 9946… 6580… 5749…
##  4 COGS                    3336… 5760… 3145… 3473… 3088… 3100… 5021… 3449… 3306…
##  5 Gross_Profit            2833… 4046… 2368… 4396… 3322… 2532… 4925… 3130… 2442…
##  6 Operating_Expenses      1862… 1277… 1637… 2342… 1715… 1565… 2866… 1428… 1323…
##  7 EBIT                    93793 2768… 73126 2053… 1606… 96688 2058… 1702… 1119…
##  8 Interest_Expense        7072  4469  12872 6518  3458  9235  7179  13524 2566 
##  9 EBT                     1302… 1588… 60254 1988… 1571… 87454 1987… 1567… 1093…
## 10 Tax_Expense             39080 47667 18076 59654 47148 26236 59616 47029 32806
## 11 Net_Income              91187 1112… 42178 1391… 1100… 61218 1391… 1097… 76548
## 12 Balance Sheet           <NA>  <NA>  <NA>  <NA>  <NA>  <NA>  <NA>  <NA>  <NA> 
## 13 Assets                  <NA>  <NA>  <NA>  <NA>  <NA>  <NA>  <NA>  <NA>  <NA> 
## 14 <NA>                    2021  <NA>  <NA>  <NA>  2022  <NA>  <NA>  <NA>  2023 
## 15 Quarter                 Q1    Q2    Q3    Q4    Q1    Q2    Q3    Q4    Q1   
## 16 Cash                    1142… 55393 1175… 63231 90856 1278… 92629 93628 1095…
## 17 Accounts_Receivable     1249… 1705… 80479 1143… 64648 80929 1282… 1237… 91860
## 18 Inventory               56891 1010… 47351 53936 51327 50100 65873 41367 52643
## 19 Current_Assets          2960… 3269… 2453… 2314… 2068… 2588… 2867… 2587… 2540…
## 20 Property_Plant_Equipme… 6336… 7974… 3502… 7422… 4726… 5398… 6015… 5726… 5393…
## 21 Total_Assets            9296… 1124… 5956… 9737… 6795… 7986… 8883… 8314… 7933…
## 22 Liability & Shareholde… <NA>  <NA>  <NA>  <NA>  <NA>  <NA>  <NA>  <NA>  <NA> 
## 23 <NA>                    2021  <NA>  <NA>  <NA>  2022  <NA>  <NA>  <NA>  2023 
## 24 Quarter                 Q1    Q2    Q3    Q4    Q1    Q2    Q3    Q4    Q1   
## 25 Accounts_Payable        64772 78770 46572 62834 38498 43219 94898 49201 58343
## 26 Short_Term_Debt         74729 1925… 84371 92974 1191… 98778 1929… 85096 77501
## 27 Current_Liabilities     1395… 2713… 1309… 1558… 1576… 1419… 2878… 1342… 1358…
## 28 Long_Term_Debt          1628… 2611… 1127… 2402… 1707… 1967… 2675… 2211… 2138…
## 29 Total_Liabilities       3023… 5324… 2437… 3960… 3283… 3387… 5553… 3554… 3496…
## 30 Common_Stock            1012… 1350… 73998 1474… 86298 1475… 1414… 1506… 1569…
## 31 Retained_Earnings       5261… 4568… 2779… 4303… 2648… 3123… 1914… 3253… 2867…
## 32 Shareholders_Equity     6273… 5919… 3519… 5777… 3511… 4599… 3329… 4759… 4436…
## 33 Total_Liabilities_and_… 9296… 1124… 5956… 9737… 6795… 7986… 8883… 8314… 7933…
## # ℹ 3 more variables: ...11 <chr>, ...12 <chr>, ...13 <chr>
print(Maclr)
## # A tibble: 33 × 13
##    `Income Statement`      ...2  ...3  ...4  ...5  ...6  ...7  ...8  ...9  ...10
##    <chr>                   <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr>
##  1 <NA>                    2021  <NA>  <NA>  <NA>  2022  <NA>  <NA>  <NA>  2023 
##  2 Quarter                 Q1    Q2    Q3    Q4    Q1    Q2    Q3    Q4    Q1   
##  3 Revenue                 5752… 5881… 8309… 6109… 6211… 8448… 5733… 8495… 8109…
##  4 COGS                    3098… 3317… 4953… 3277… 2972… 4590… 3068… 3521… 4031…
##  5 Gross_Profit            2833… 2563… 3356… 2832… 3238… 3858… 2665… 4974… 4077…
##  6 Operating_Expenses      1552… 1277… 2310… 1744… 1737… 2269… 1537… 2185… 1947…
##  7 EBIT                    93793 1285… 1045… 1087… 1501… 1589… 1127… 2788… 2130…
##  8 Interest_Expense        9862  4469  5103  4117  6447  13040 6570  7506  7960 
##  9 EBT                     1002… 1176… 99459 1046… 1436… 1458… 1061… 2713… 2050…
## 10 Tax_Expense             30070 35305 29838 31395 43106 43761 31845 81409 61521
## 11 Net_Income              70164 82378 69621 73254 1005… 1021… 74304 1899… 1435…
## 12 Balance Sheet           <NA>  <NA>  <NA>  <NA>  <NA>  <NA>  <NA>  <NA>  <NA> 
## 13 Assets                  <NA>  <NA>  <NA>  <NA>  <NA>  <NA>  <NA>  <NA>  <NA> 
## 14 <NA>                    2021  <NA>  <NA>  <NA>  2022  <NA>  <NA>  <NA>  2023 
## 15 Quarter                 Q1    Q2    Q3    Q4    Q1    Q2    Q3    Q4    Q1   
## 16 Cash                    1143… 52048 82547 88720 1143… 52048 82547 88720 60878
## 17 Accounts_Receivable     61252 1082… 1121… 97200 61252 1082… 1121… 97200 1142…
## 18 Inventory               44240 66052 95321 49054 44240 66052 95321 49054 34612
## 19 Current_Assets          2198… 2263… 2900… 2349… 2198… 2263… 2900… 2349… 2097…
## 20 Property_Plant_Equipme… 3687… 3010… 4382… 4618… 3687… 3010… 4382… 4618… 3291…
## 21 Total_Assets            5886… 5273… 7283… 6968… 5886… 5273… 7283… 6968… 5389…
## 22 Liability & Shareholde… <NA>  <NA>  <NA>  <NA>  <NA>  <NA>  <NA>  <NA>  <NA> 
## 23 <NA>                    2021  <NA>  <NA>  <NA>  2022  <NA>  <NA>  <NA>  2023 
## 24 Quarter                 Q1    Q2    Q3    Q4    Q1    Q2    Q3    Q4    Q1   
## 25 Accounts_Payable        34330 42400 65110 58549 34330 42400 65110 58549 50651
## 26 Short_Term_Debt         58318 97854 1076… 1213… 58318 97854 1076… 1213… 1194…
## 27 Current_Liabilities     92649 1402… 1727… 1798… 92649 1402… 1727… 1798… 1700…
## 28 Long_Term_Debt          1975… 1977… 2984… 1232… 1975… 1977… 2984… 1232… 1898…
## 29 Total_Liabilities       2901… 3380… 4712… 3030… 2901… 3380… 4712… 3030… 3598…
## 30 Common_Stock            66111 71068 82858 97538 66111 71068 82858 97538 55473
## 31 Retained_Earnings       2322… 1182… 1742… 2961… 2322… 1182… 1742… 2961… 1235…
## 32 Shareholders_Equity     2984… 1893… 2570… 3937… 2984… 1893… 2570… 3937… 1790…
## 33 Total_Liabilities_and_… 5886… 5273… 7283… 6968… 5886… 5273… 7283… 6968… 5389…
## # ℹ 3 more variables: ...11 <chr>, ...12 <chr>, ...13 <chr>
print(Dain)
## # A tibble: 33 × 13
##    `Income Statement`      ...2  ...3  ...4  ...5  ...6  ...7  ...8  ...9  ...10
##    <chr>                   <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr>
##  1 <NA>                    2021  <NA>  <NA>  <NA>  2022  <NA>  <NA>  <NA>  2023 
##  2 Quarter                 Q1    Q2    Q3    Q4    Q1    Q2    Q3    Q4    Q1   
##  3 Revenue                 5822… 6001… 9503… 9706… 5126… 5620… 6903… 8925… 7864…
##  4 COGS                    3163… 3345… 5565… 4716… 2145… 2685… 4022… 5346… 3462…
##  5 Gross_Profit            2833… 2656… 3938… 4990… 2980… 2934… 2881… 3578… 4402…
##  6 Operating_Expenses      1740… 1277… 2722… 2439… 1457… 1369… 1470… 2096… 2197…
##  7 EBIT                    93793 1378… 1215… 2550… 1522… 1565… 1410… 1482… 2204…
##  8 Interest_Expense        9789  4469  4735  19283 2081  8831  9876  7790  6859 
##  9 EBT                     82120 1390… 1168… 2357… 1502… 1477… 1311… 1404… 2136…
## 10 Tax_Expense             24636 41721 35040 70730 45061 44312 39358 42136 64083
## 11 Net_Income              57484 97348 81761 1650… 1051… 1033… 91836 98317 1495…
## 12 Balance Sheet           <NA>  <NA>  <NA>  <NA>  <NA>  <NA>  <NA>  <NA>  <NA> 
## 13 Assets                  <NA>  <NA>  <NA>  <NA>  <NA>  <NA>  <NA>  <NA>  <NA> 
## 14 <NA>                    2021  <NA>  <NA>  <NA>  2022  <NA>  <NA>  <NA>  2023 
## 15 Quarter                 Q1    Q2    Q3    Q4    Q1    Q2    Q3    Q4    Q1   
## 16 Cash                    73285 1182… 81358 68008 1099… 86083 1285… 1044… 77457
## 17 Accounts_Receivable     79141 1040… 1410… 1297… 71892 91393 87550 1056… 1434…
## 18 Inventory               62189 53933 1019… 84692 33210 37635 66957 73960 38354
## 19 Current_Assets          2146… 2762… 3242… 2824… 2150… 2151… 2830… 2840… 2592…
## 20 Property_Plant_Equipme… 3023… 3417… 5841… 6957… 4114… 5470… 4828… 7129… 6157…
## 21 Total_Assets            5169… 6180… 9084… 9781… 6265… 7621… 7659… 9969… 8749…
## 22 Liability & Shareholde… <NA>  <NA>  <NA>  <NA>  <NA>  <NA>  <NA>  <NA>  <NA> 
## 23 <NA>                    2021  <NA>  <NA>  <NA>  2022  <NA>  <NA>  <NA>  2023 
## 24 Quarter                 Q1    Q2    Q3    Q4    Q1    Q2    Q3    Q4    Q1   
## 25 Accounts_Payable        57002 39148 85216 50292 28161 27863 43686 91711 64621
## 26 Short_Term_Debt         1131… 65784 1133… 1067… 1022… 96623 82917 1734… 85810
## 27 Current_Liabilities     1701… 1049… 1986… 1570… 1304… 1244… 1266… 2651… 1504…
## 28 Long_Term_Debt          1428… 1836… 3037… 2536… 1169… 1725… 1927… 2608… 1686…
## 29 Total_Liabilities       3130… 2886… 5023… 4107… 2473… 2970… 3193… 5259… 3190…
## 30 Common_Stock            82810 1199… 1528… 1151… 88604 1285… 93567 1796… 1492…
## 31 Retained_Earnings       1210… 2094… 2531… 4523… 2905… 3365… 3529… 2913… 4066…
## 32 Shareholders_Equity     2039… 3293… 4060… 5674… 3791… 4650… 4465… 4710… 5559…
## 33 Total_Liabilities_and_… 5169… 6180… 9084… 9781… 6265… 7621… 7659… 9969… 8749…
## # ℹ 3 more variables: ...11 <chr>, ...12 <chr>, ...13 <chr>

After refining and adjusting the values, we are now ready to proceed with the analysis.

In the file provided below, you will find three sheets:

DATA: This sheet contains all the measures and adjustments made.

DATA <- read_excel("D:/Project/Measures.xlsx", sheet = 1)
print(DATA)
## # A tibble: 48 × 27
##    Company Year  `Current Ratio` `Quick Ratio` `Debt-To-equity` `Debt-To-Assets`
##    <chr>   <chr>           <dbl>         <dbl>            <dbl>            <dbl>
##  1 TECH    21-Q1           1.60          1.19             0.657            0.359
##  2 TECH    21-Q2           1.71          1.34             0.423            0.282
##  3 TECH    21-Q3           1.22          0.862            0.465            0.299
##  4 TECH    21-Q4           1.91          1.48             0.838            0.426
##  5 TECH    22-Q1           1.23          0.953            0.666            0.379
##  6 TECH    22-Q2           1.78          1.51             1.30             0.511
##  7 TECH    22-Q3           1.71          1.33             0.561            0.332
##  8 TECH    22-Q4           1.48          1.03             0.853            0.414
##  9 TECH    23-Q1           1.42          0.954            0.938            0.448
## 10 TECH    23-Q2           2.14          1.77             0.503            0.314
## 11 TECH    23-Q3           1.69          1.46             0.526            0.326
## 12 TECH    23-Q4           1.16          0.714            1.91             0.592
## 13 SPEED   21-Q1           2.12          1.71             0.379            0.256
## 14 SPEED   21-Q2           1.20          0.833            0.766            0.403
## 15 SPEED   21-Q3           1.87          1.51             0.560            0.331
## 16 SPEED   21-Q4           1.49          1.14             0.577            0.342
## 17 SPEED   22-Q1           1.31          0.987            0.826            0.427
## 18 SPEED   22-Q2           1.82          1.47             0.642            0.370
## 19 SPEED   22-Q3           0.996         0.767            1.38             0.518
## 20 SPEED   22-Q4           1.93          1.62             0.643            0.368
## 21 SPEED   23-Q1           1.87          1.48             0.657            0.367
## 22 SPEED   23-Q2           1.39          1.12             1.19             0.508
## 23 SPEED   23-Q3           1.17          0.894            0.495            0.311
## 24 SPEED   23-Q4           1.69          1.49             0.995            0.471
## 25 MACLR   21-Q1           2.37          1.90             0.857            0.435
## 26 MACLR   21-Q2           1.61          1.14             1.56             0.561
## 27 MACLR   21-Q3           1.68          1.13             1.58             0.558
## 28 MACLR   21-Q4           1.31          1.03             0.621            0.351
## 29 MACLR   22-Q1           2.37          1.90             0.857            0.435
## 30 MACLR   22-Q2           1.61          1.14             1.56             0.561
## 31 MACLR   22-Q3           1.68          1.13             1.58             0.558
## 32 MACLR   22-Q4           1.31          1.03             0.621            0.351
## 33 MACLR   23-Q1           1.23          1.03             1.73             0.574
## 34 MACLR   23-Q2           1.48          1.19             0.425            0.274
## 35 MACLR   23-Q3           2.40          1.83             0.495            0.314
## 36 MACLR   23-Q4           1.59          1.28             0.902            0.435
## 37 DAIN    21-Q1           1.26          0.896            1.26             0.495
## 38 DAIN    21-Q2           2.63          2.12             0.757            0.404
## 39 DAIN    21-Q3           1.63          1.12             1.03             0.459
## 40 DAIN    21-Q4           1.80          1.26             0.635            0.368
## 41 DAIN    22-Q1           1.65          1.39             0.578            0.350
## 42 DAIN    22-Q2           1.73          1.43             0.579            0.353
## 43 DAIN    22-Q3           2.24          1.71             0.617            0.360
## 44 DAIN    22-Q4           1.07          0.792            0.922            0.436
## 45 DAIN    23-Q1           1.72          1.47             0.458            0.291
## 46 DAIN    23-Q2           1.02          0.823            1.36             0.528
## 47 DAIN    23-Q3           1.24          1.02             0.717            0.395
## 48 DAIN    23-Q4           1.82          1.50             1.10             0.498
## # ℹ 21 more variables: `Debt-To-Captial` <dbl>, `Interest-Coverage` <dbl>,
## #   `Recivables Turnover` <dbl>, DSO <dbl>, `Payables Turnover` <dbl>,
## #   DPO <dbl>, `Inventory Turnover` <dbl>, DOH <dbl>,
## #   `Cash Conversion Cycle` <dbl>, `Gross Profit Margin` <dbl>,
## #   `EBIT Margin` <dbl>, `Tax Burden` <dbl>, `Interest Burden` <dbl>,
## #   `Net Profit Margin` <dbl>, `Asset-Turnover` <dbl>,
## #   `Return-On-Assets` <dbl>, `Equity-Multiplier` <dbl>, …

CHANGES: This sheet includes each measure along with the percentage change compared to the previous period.

Changes <- read_excel("D:/Project/Measures.xlsx", sheet = 2)
print(Changes)
## # A tibble: 48 × 27
##    Company `CHANGES %` `Current Ratio` `Quick Ratio` `Debt-To-equity`
##    <chr>   <chr>                 <dbl>         <dbl>            <dbl>
##  1 Tech    21-Q1                0            0               0       
##  2 Tech    21-Q2/21-Q1          0.0653       0.120          -0.356   
##  3 Tech    21-Q3/21-Q2         -0.287       -0.354           0.0990  
##  4 Tech    21-Q4/21-Q3          0.574        0.722           0.804   
##  5 Tech    22-Q1/21-Q4         -0.359       -0.358          -0.205   
##  6 Tech    22-Q2/22-Q1          0.453        0.583           0.955   
##  7 Tech    22-Q3/22-Q2         -0.0378      -0.116          -0.569   
##  8 Tech    22-Q4/22-Q3         -0.134       -0.227           0.519   
##  9 Tech    23-Q1/22-Q4         -0.0409      -0.0736          0.100   
## 10 Tech    23-Q2/23-Q1          0.503        0.858          -0.463   
## 11 Tech    23-Q3/23-Q2         -0.213       -0.176           0.0449  
## 12 Tech    23-Q4/23-Q3         -0.312       -0.512           2.64    
## 13 Speed   21-Q1                0            0               0       
## 14 Speed   21-Q2/21-Q1         -0.432       -0.514           1.02    
## 15 Speed   21-Q3/21-Q2          0.555        0.816          -0.269   
## 16 Speed   21-Q4/21-Q3         -0.207       -0.246           0.0296  
## 17 Speed   22-Q1/21-Q4         -0.117       -0.134           0.431   
## 18 Speed   22-Q2/22-Q1          0.389        0.490          -0.222   
## 19 Speed   22-Q3/22-Q2         -0.454       -0.478           1.15    
## 20 Speed   22-Q4/22-Q3          0.934        1.11           -0.535   
## 21 Speed   23-Q1/22-Q4         -0.0294      -0.0841          0.0206  
## 22 Speed   23-Q2/23-Q1         -0.258       -0.244           0.811   
## 23 Speed   23-Q3/23-Q2         -0.156       -0.203          -0.584   
## 24 Speed   23-Q4/23-Q3          0.445        0.671           1.01    
## 25 Maclr   21-Q1                0            0               0       
## 26 Maclr   21-Q2/21-Q1         -0.320       -0.397           0.821   
## 27 Maclr   21-Q3/21-Q2          0.0404      -0.0137          0.0118  
## 28 Maclr   21-Q4/21-Q3         -0.222       -0.0830         -0.607   
## 29 Maclr   22-Q1/21-Q4          0.816        0.833           0.381   
## 30 Maclr   22-Q2/22-Q1         -0.320       -0.397           0.821   
## 31 Maclr   22-Q3/22-Q2          0.0404      -0.0137          0.0118  
## 32 Maclr   22-Q4/22-Q3         -0.222       -0.0830         -0.607   
## 33 Maclr   23-Q1/22-Q4         -0.0557      -0.00344         1.78    
## 34 Maclr   23-Q2/23-Q1          0.203        0.155          -0.754   
## 35 Maclr   23-Q3/23-Q2          0.614        0.537           0.165   
## 36 Maclr   23-Q4/23-Q3         -0.337       -0.301           0.824   
## 37 Dain    21-Q1                0            0               0       
## 38 Dain    21-Q2/21-Q1          1.09         1.37           -0.397   
## 39 Dain    21-Q3/21-Q2         -0.380       -0.472           0.357   
## 40 Dain    21-Q4/21-Q3          0.101        0.124          -0.382   
## 41 Dain    22-Q1/21-Q4         -0.0827       0.108          -0.0896  
## 42 Dain    22-Q2/22-Q1          0.0477       0.0223          0.000949
## 43 Dain    22-Q3/22-Q2          0.294        0.197           0.0668  
## 44 Dain    22-Q4/22-Q3         -0.521       -0.536           0.493   
## 45 Dain    23-Q1/22-Q4          0.608        0.853          -0.504   
## 46 Dain    23-Q2/23-Q1         -0.409       -0.439           1.96    
## 47 Dain    23-Q3/23-Q2          0.219        0.236          -0.471   
## 48 Dain    23-Q4/23-Q3          0.463        0.470           0.540   
## # ℹ 22 more variables: `Debt-To-Assets` <dbl>, `Debt-To-Captial` <dbl>,
## #   `Interest-Coverage` <dbl>, `Recivables Turnover` <dbl>, DSO <dbl>,
## #   `Payables Turnover` <dbl>, DPO <dbl>, `Inventory Turnover` <dbl>,
## #   DOH <dbl>, `Cash Conversion Cycle` <dbl>, `Gross Profit Margin` <dbl>,
## #   `EBIT Margin` <dbl>, `Tax Burden` <dbl>, `Interest Burden` <dbl>,
## #   `Net Profit Margin` <dbl>, `Asset-Turnover` <dbl>,
## #   `Return-On-Assets` <dbl>, `Equity-Multiplier` <dbl>, …

RATIOS SHEET: This sheet contains the measures for each quarter of the year for each company Which is comparable.

Which Contain Four Main Measures

First: Financial_Health Measures

library(ggplot2)
library(plotly)
## 
## Attaching package: 'plotly'
## The following object is masked from 'package:ggplot2':
## 
##     last_plot
## The following object is masked from 'package:stats':
## 
##     filter
## The following object is masked from 'package:graphics':
## 
##     layout
library(dplyr)
library(tidyr)
# Define the data
quarters <- c('21-Q1', '21-Q2', '21-Q3', '21-Q4', 
              '22-Q1', '22-Q2', '22-Q3', '22-Q4', 
              '23-Q1', '23-Q2', '23-Q3', '23-Q4')

company_tech_gross <- c(44, 54, 40, 48, 51, 46, 47, 47, 45, 44, 55, 40)
company_speed_gross <- c(43, 41, 43, 56, 52, 45, 50, 48, 42, 58, 57, 56)
company_maclr_gross <- c(49, 44, 40, 46, 52, 46, 46, 59, 50, 60, 45, 52)
company_dain_gross <- c(49, 44, 41, 51, 58, 52, 42, 40, 56, 50, 51, 54)

company_tech_ebit <- c(14, 31, 17, 26, 22, 23, 27, 20, 17, 18, 31, 11)
company_speed_ebit <- c(14, 28, 13, 26, 25, 17, 21, 26, 19, 38, 33, 33)
company_maclr_ebit <- c(16, 22, 13, 18, 24, 19, 20, 33, 26, 38, 16, 30)
company_dain_ebit <- c(16, 23, 13, 26, 30, 28, 20, 17, 28, 29, 29, 29)

company_tech_net <- c(10, 21, 11, 18, 15, 15, 18, 13, 12, 13, 21, 7)
company_speed_net <- c(14, 11, 8, 18, 17, 11, 14, 17, 13, 26, 22, 22)
company_maclr_net <- c(12, 14, 8, 12, 16, 12, 13, 22, 18, 26, 11, 19)
company_dain_net <- c(10, 16, 9, 17, 21, 18, 13, 11, 19, 20, 20, 19)

data_gross <- data.frame(
  Quarter = quarters,
  Tech = company_tech_gross,
  Speed = company_speed_gross,
  Maclr = company_maclr_gross,
  Dain = company_dain_gross,
  Measure = "Gross_Profit_Margin"
)

data_ebit <- data.frame(
  Quarter = quarters,
  Tech = company_tech_ebit,
  Speed = company_speed_ebit,
  Maclr = company_maclr_ebit,
  Dain = company_dain_ebit,
  Measure = "EBIT_Margin"
)

data_net <- data.frame(
  Quarter = quarters,
  Tech = company_tech_net,
  Speed = company_speed_net,
  Maclr = company_maclr_net,
  Dain = company_dain_net,
  Measure = "Net_Profit_Margin"
)

data <- rbind(data_gross, data_ebit, data_net)

# Use pivot_longer to reshape the data
data <- data %>% pivot_longer(cols = c(Tech, Speed, Maclr, Dain), names_to = "Company", values_to = "Value")
# Filter data for Gross Profit Margin
data_gross_filtered <- data %>% filter(Measure == "Gross_Profit_Margin")

# Plot Gross Profit Margin
p_gross <- ggplot(data_gross_filtered, aes(x = Quarter, y = Value, fill = Company)) +
  geom_bar(stat = "identity", position = "dodge") +
  labs(title = "Gross Profit Margin Comparison",
       x = "Year-Quarter",
       y = "Gross Profit Margin (%)",
       fill = "Company") +
  theme_minimal() +
  theme(axis.text.x = element_text(angle = 45, hjust = 1))

ggplotly(p_gross)
# Filter data for EBIT Margin
data_ebit_filtered <- data %>% filter(Measure == "EBIT_Margin")

# Plot EBIT Margin
p_ebit <- ggplot(data_ebit_filtered, aes(x = Quarter, y = Value, fill = Company)) +
  geom_bar(stat = "identity", position = "dodge") +
  labs(title = "EBIT Margin Comparison",
       x = "Year-Quarter",
       y = "EBIT Margin (%)",
       fill = "Company") +
  theme_minimal() +
  theme(axis.text.x = element_text(angle = 45, hjust = 1))

ggplotly(p_ebit)
# Filter data for Net Profit Margin
data_net_filtered <- data %>% filter(Measure == "Net_Profit_Margin")

# Plot Net Profit Margin
p_net <- ggplot(data_net_filtered, aes(x = Quarter, y = Value, fill = Company)) +
  geom_bar(stat = "identity", position = "dodge") +
  labs(title = "Net Profit Margin Comparison",
       x = "Year-Quarter",
       y = "Net Profit Margin (%)",
       fill = "Company") +
  theme_minimal() +
  theme(axis.text.x = element_text(angle = 45, hjust = 1))

ggplotly(p_net)
# Load required packages
library(ggplot2)
library(plotly)
library(dplyr)
library(tidyr)
# Define the data
quarters <- c('21-Q1', '21-Q2', '21-Q3', '21-Q4', 
              '22-Q1', '22-Q2', '22-Q3', '22-Q4', 
              '23-Q1', '23-Q2', '23-Q3', '23-Q4')

company_tech_roa <- c(9, 15, 10, 33, 19, 11, 13, 27, 10, 8, 20, 16)
company_speed_roa <- c(10, 13, 5, 29, 15, 7, 16, 26, 10, 21, 26, 45)
company_maclr_roa <- c(12, 16, 10, 11, 17, 19, 10, 27, 27, 16, 7, 17)
company_dain_roa <- c(11, 16, 9, 17, 17, 14, 12, 10, 17, 20, 18, 19)

company_tech_roe <- c(17, 27, 14, 32, 26, 37, 22, 27, 20, 17, 25, 25)
company_speed_roe <- c(15, 19, 12, 24, 31, 13, 42, 23, 17, 60, 31, 47)
company_maclr_roe <- c(24, 44, 27, 19, 34, 54, 29, 48, 80, 24, 11, 35)
company_dain_roe <- c(28, 30, 20, 29, 28, 22, 21, 21, 27, 51, 33, 43)

company_tech_oroa <- c(28, 26, 16, 21, 24, 17, 25, 20, 13, 15, 29, 9)
company_speed_oroa <- c(20, 27, 9, 26, 19, 13, 24, 20, 14, 35, 35, 23)
company_maclr_oroa <- c(32, 23, 17, 15, 23, 28, 18, 39, 34, 30, 9, 26)
company_dain_oroa <- c(36, 24, 16, 27, 19, 23, 18, 17, 24, 29, 25, 30)

data_roa <- data.frame(
  Quarter = quarters,
  Tech = company_tech_roa,
  Speed = company_speed_roa,
  Maclr = company_maclr_roa,
  Dain = company_dain_roa,
  Measure = "ROA"
)

data_roe <- data.frame(
  Quarter = quarters,
  Tech = company_tech_roe,
  Speed = company_speed_roe,
  Maclr = company_maclr_roe,
  Dain = company_dain_roe,
  Measure = "ROE"
)

data_oroa <- data.frame(
  Quarter = quarters,
  Tech = company_tech_oroa,
  Speed = company_speed_oroa,
  Maclr = company_maclr_oroa,
  Dain = company_dain_oroa,
  Measure = "Operating_ROA"
)

data <- rbind(data_roa, data_roe, data_oroa)

# Use pivot_longer to reshape the data
data <- data %>% pivot_longer(cols = c(Tech, Speed, Maclr, Dain), names_to = "Company", values_to = "Value")
# Filter data for ROA
data_roa_filtered <- data %>% filter(Measure == "ROA")

# Plot ROA
p_roa <- ggplot(data_roa_filtered, aes(x = Quarter, y = Value, fill = Company)) +
  geom_bar(stat = "identity", position = "dodge") +
  labs(title = "ROA Comparison",
       x = "Year-Quarter",
       y = "ROA (%)",
       fill = "Company") +
  theme_minimal() +
  theme(axis.text.x = element_text(angle = 45, hjust = 1))

ggplotly(p_roa)
# Filter data for ROE
data_roe_filtered <- data %>% filter(Measure == "ROE")

# Plot ROE
p_roe <- ggplot(data_roe_filtered, aes(x = Quarter, y = Value, fill = Company)) +
  geom_bar(stat = "identity", position = "dodge") +
  labs(title = "ROE Comparison",
       x = "Year-Quarter",
       y = "ROE (%)",
       fill = "Company") +
  theme_minimal() +
  theme(axis.text.x = element_text(angle = 45, hjust = 1))

ggplotly(p_roe)
# Filter data for Operating ROA
data_oroa_filtered <- data %>% filter(Measure == "Operating_ROA")

# Plot Operating ROA
p_oroa <- ggplot(data_oroa_filtered, aes(x = Quarter, y = Value, fill = Company)) +
  geom_bar(stat = "identity", position = "dodge") +
  labs(title = "Operating ROA Comparison",
       x = "Year-Quarter",
       y = "Operating ROA (%)",
       fill = "Company") +
  theme_minimal() +
  theme(axis.text.x = element_text(angle = 45, hjust = 1))

ggplotly(p_oroa)

Second: Solvency Measures

library(ggplot2)
library(plotly)
library(dplyr)
library(tidyr)
# Define the data
quarters <- c('21-Q1', '21-Q2', '21-Q3', '21-Q4', 
              '22-Q1', '22-Q2', '22-Q3', '22-Q4', 
              '23-Q1', '23-Q2', '23-Q3', '23-Q4')

company_tech_debt_to_assets <- c(36, 28, 30, 43, 38, 51, 33, 41, 45, 31, 33, 59)
company_speed_debt_to_assets <- c(26, 40, 33, 34, 43, 37, 52, 37, 37, 51, 31, 47)
company_maclr_debt_to_assets <- c(43, 56, 56, 35, 43, 56, 56, 35, 57, 27, 31, 43)
company_dain_debt_to_assets <- c(50, 40, 46, 37, 35, 35, 36, 44, 29, 53, 39, 50)

company_tech_debt_to_equity <- c(66, 42, 46, 84, 67, 130, 56, 85, 94, 50, 53, 191)
company_speed_debt_to_equity <- c(38, 77, 56, 58, 83, 64, 138, 64, 66, 119, 50, 99)
company_maclr_debt_to_equity <- c(86, 156, 158, 62, 86, 156, 158, 62, 173, 42, 49, 90)
company_dain_debt_to_equity <- c(126, 76, 103, 64, 58, 58, 62, 92, 46, 136, 72, 110)

company_tech_debt_to_capital <- c(40, 30, 32, 46, 40, 57, 36, 46, 48, 33, 34, 66)
company_speed_debt_to_capital <- c(27, 43, 36, 37, 45, 39, 58, 39, 40, 54, 33, 50)
company_maclr_debt_to_capital <- c(46, 61, 61, 38, 46, 61, 61, 38, 63, 30, 33, 47)
company_dain_debt_to_capital <- c(56, 43, 51, 39, 37, 37, 38, 48, 31, 58, 42, 52)

data_debt_to_assets <- data.frame(
  Quarter = quarters,
  Tech = company_tech_debt_to_assets,
  Speed = company_speed_debt_to_assets,
  Maclr = company_maclr_debt_to_assets,
  Dain = company_dain_debt_to_assets,
  Measure = "Debt_To_Assets"
)

data_debt_to_equity <- data.frame(
  Quarter = quarters,
  Tech = company_tech_debt_to_equity,
  Speed = company_speed_debt_to_equity,
  Maclr = company_maclr_debt_to_equity,
  Dain = company_dain_debt_to_equity,
  Measure = "Debt_To_Equity"
)

data_debt_to_capital <- data.frame(
  Quarter = quarters,
  Tech = company_tech_debt_to_capital,
  Speed = company_speed_debt_to_capital,
  Maclr = company_maclr_debt_to_capital,
  Dain = company_dain_debt_to_capital,
  Measure = "Debt_To_Capital"
)

data <- bind_rows(data_debt_to_assets, data_debt_to_equity, data_debt_to_capital)

data <- data %>% pivot_longer(cols = c(Tech, Speed, Maclr, Dain), names_to = "Company", values_to = "Value")
# Filter data for Debt-To-Assets
data_debt_to_assets_filtered <- data %>% filter(Measure == "Debt_To_Assets")

# Plot Debt-To-Assets
p_debt_to_assets <- ggplot(data_debt_to_assets_filtered, aes(x = Quarter, y = Value, fill = Company)) +
  geom_bar(stat = "identity", position = "dodge") +
  labs(title = "Debt-To-Assets Comparison",
       x = "Year-Quarter",
       y = "Debt-To-Assets (%)",
       fill = "Company") +
  theme_minimal() +
  theme(axis.text.x = element_text(angle = 45, hjust = 1))

ggplotly(p_debt_to_assets)
# Filter data for Debt-To-Equity
data_debt_to_equity_filtered <- data %>% filter(Measure == "Debt_To_Equity")

# Plot Debt-To-Equity
p_debt_to_equity <- ggplot(data_debt_to_equity_filtered, aes(x = Quarter, y = Value, fill = Company)) +
  geom_bar(stat = "identity", position = "dodge") +
  labs(title = "Debt-To-Equity Comparison",
       x = "Year-Quarter",
       y = "Debt-To-Equity (%)",
       fill = "Company") +
  theme_minimal() +
  theme(axis.text.x = element_text(angle = 45, hjust = 1))

ggplotly(p_debt_to_equity)
# Filter data for Debt-To-Capital
data_debt_to_capital_filtered <- data %>% filter(Measure == "Debt_To_Capital")

# Plot Debt-To-Capital
p_debt_to_capital <- ggplot(data_debt_to_capital_filtered, aes(x = Quarter, y = Value, fill = Company)) +
  geom_bar(stat = "identity", position = "dodge") +
  labs(title = "Debt-To-Capital Comparison",
       x = "Year-Quarter",
       y = "Debt-To-Capital (%)",
       fill = "Company") +
  theme_minimal() +
  theme(axis.text.x = element_text(angle = 45, hjust = 1))

ggplotly(p_debt_to_capital)

Third: Activity Measures

library(ggplot2)
library(plotly)
library(dplyr)
library(tidyr)
# Define the data
quarters <- c('21-Q1', '21-Q2', '21-Q3', '21-Q4', 
              '22-Q1', '22-Q2', '22-Q3', '22-Q4', 
              '23-Q1', '23-Q2', '23-Q3', '23-Q4')

# Data for each measure
company_tech_receivable <- c(19.28, 9.09, 10.68, 7.02, 5.89, 8.58, 4.06, 5.09, 13.71, 6.92, 3.64, 10.73)
company_speed_receivable <- c(10.52, 6.64, 4.39, 4.82, 12.17, 7.92, 4.39, 8.04, 7.16, 5.41, 6.24, 11.57)
company_maclr_receivable <- c(18.78, 6.94, 7.54, 8.55, 9.97, 5.74, 7.53, 5.90, 7.43, 6.24, 8.84, 3.66)
company_dain_receivable <- c(14.71, 6.55, 7.75, 7.32, 13.50, 5.61, 6.42, 6.54, 6.22, 5.62, 7.21, 4.86)

company_tech_payable <- c(11.36, 5.30, 10.47, 7.01, 9.05, 5.63, 5.73, 7.80, 12.32, 15.27, 7.33, 6.88)
company_speed_payable <- c(10.30, 8.03, 5.02, 6.35, 6.10, 7.59, 7.27, 4.50, 11.34, 12.63, 7.23, 5.08)
company_maclr_payable <- c(18.05, 8.65, 9.21, 5.30, 6.40, 11.97, 5.71, 6.08, 15.92, 6.01, 4.62, 6.31)
company_dain_payable <- c(11.10, 6.96, 8.95, 6.96, 5.47, 9.59, 11.25, 9.87, 10.72, 11.89, 5.98, 9.08)

company_tech_inventory <- c(12.11, 5.39, 9.49, 5.58, 7.46, 6.80, 7.25, 6.58, 4.47, 5.81, 8.76, 6.85)
company_speed_inventory <- c(11.73, 7.30, 4.24, 6.86, 5.87, 6.11, 8.66, 6.43, 7.04, 6.18, 8.61, 6.05)
company_maclr_inventory <- c(14.01, 6.02, 6.14, 4.54, 6.37, 8.32, 3.80, 4.88, 9.64, 5.99, 5.24, 6.41)
company_dain_inventory <- c(10.17, 5.76, 7.14, 5.06, 3.64, 7.58, 7.69, 7.59, 6.17, 10.79, 8.19, 7.74)

company_tech_cash <- c(16.95, 39.09, 37.78, 65.33, 70.60, 31.40, 76.44, 80.36, 78.61, 91.63, 92.15, 34.22)
company_speed_cash <- c(30.39, 59.54, 96.43, 71.40, 32.30, 57.68, 75.05, 21.08, 70.64, 97.63, 50.35, 20.02)
company_maclr_cash <- c(25.27, 71.06, 68.26, 54.23, 36.85, 76.96, 80.50, 76.71, 64.05, 58.65, 32.01, 98.85)
company_dain_cash <- c(27.80, 66.60, 57.39, 69.60, 60.59, 75.15, 71.85, 66.98, 83.79, 68.07, 34.10, 82.05)

data_receivable <- data.frame(
  Quarter = quarters,
  Company = rep(c('Tech', 'Speed', 'Maclr', 'Dain'), each = length(quarters)),
  Value = c(company_tech_receivable, company_speed_receivable, company_maclr_receivable, company_dain_receivable),
  Measure = 'Receivable Turnover'
)

data_payable <- data.frame(
  Quarter = quarters,
  Company = rep(c('Tech', 'Speed', 'Maclr', 'Dain'), each = length(quarters)),
  Value = c(company_tech_payable, company_speed_payable, company_maclr_payable, company_dain_payable),
  Measure = 'Payable Turnover'
)

data_inventory <- data.frame(
  Quarter = quarters,
  Company = rep(c('Tech', 'Speed', 'Maclr', 'Dain'), each = length(quarters)),
  Value = c(company_tech_inventory, company_speed_inventory, company_maclr_inventory, company_dain_inventory),
  Measure = 'Inventory Turnover'
)

data_cash <- data.frame(
  Quarter = quarters,
  Company = rep(c('Tech', 'Speed', 'Maclr', 'Dain'), each = length(quarters)),
  Value = c(company_tech_cash, company_speed_cash, company_maclr_cash, company_dain_cash),
  Measure = 'Cash Conversion Cycle'
)

data <- bind_rows(data_receivable, data_payable, data_inventory, data_cash)
# Filter data for Receivable Turnover
data_receivable_filtered <- data %>% filter(Measure == "Receivable Turnover")

# Plot Receivable Turnover
p_receivable <- ggplot(data_receivable_filtered, aes(x = Quarter, y = Value, fill = Company)) +
  geom_bar(stat = "identity", position = "dodge") +
  labs(title = "Receivable Turnover Comparison",
       x = "Year-Quarter",
       y = "Receivable Turnover",
       fill = "Company") +
  theme_minimal() +
  theme(axis.text.x = element_text(angle = 45, hjust = 1))

ggplotly(p_receivable)
# Filter data for Payable Turnover
data_payable_filtered <- data %>% filter(Measure == "Payable Turnover")

# Plot Payable Turnover
p_payable <- ggplot(data_payable_filtered, aes(x = Quarter, y = Value, fill = Company)) +
  geom_bar(stat = "identity", position = "dodge") +
  labs(title = "Payable Turnover Comparison",
       x = "Year-Quarter",
       y = "Payable Turnover",
       fill = "Company") +
  theme_minimal() +
  theme(axis.text.x = element_text(angle = 45, hjust = 1))

ggplotly(p_payable)
# Filter data for Inventory Turnover
data_inventory_filtered <- data %>% filter(Measure == "Inventory Turnover")

# Plot Inventory Turnover
p_inventory <- ggplot(data_inventory_filtered, aes(x = Quarter, y = Value, fill = Company)) +
  geom_bar(stat = "identity", position = "dodge") +
  labs(title = "Inventory Turnover Comparison",
       x = "Year-Quarter",
       y = "Inventory Turnover",
       fill = "Company") +
  theme_minimal() +
  theme(axis.text.x = element_text(angle = 45, hjust = 1))

ggplotly(p_inventory)
# Filter data for Cash Conversion Cycle
data_cash_filtered <- data %>% filter(Measure == "Cash Conversion Cycle")

# Plot Cash Conversion Cycle
p_cash <- ggplot(data_cash_filtered, aes(x = Quarter, y = Value, fill = Company)) +
  geom_bar(stat = "identity", position = "dodge") +
  labs(title = "Cash Conversion Cycle Comparison",
       x = "Year-Quarter",
       y = "Cash Conversion Cycle",
       fill = "Company") +
  theme_minimal() +
  theme(axis.text.x = element_text(angle = 45, hjust = 1))

ggplotly(p_cash)

Finally: Liquidity Measures

library(ggplot2)
library(dplyr)
library(tidyr)
# Define the data
quarters <- c('21-Q1', '21-Q2', '21-Q3', '21-Q4', 
              '22-Q1', '22-Q2', '22-Q3', '22-Q4', 
              '23-Q1', '23-Q2', '23-Q3', '23-Q4')

# Current Ratio data
current_ratio_tech <- c(1.60, 1.71, 1.22, 1.91, 1.23, 1.78, 1.71, 1.48, 1.42, 2.14, 1.69, 1.16)
current_ratio_speed <- c(2.12, 1.20, 1.87, 1.49, 1.31, 1.82, 1.00, 1.93, 1.87, 1.39, 1.17, 1.69)
current_ratio_maclr <- c(2.37, 1.61, 1.68, 1.31, 2.37, 1.61, 1.68, 1.31, 1.23, 1.48, 2.40, 1.59)
current_ratio_dain <- c(1.26, 2.63, 1.63, 1.80, 1.65, 1.73, 2.24, 1.07, 1.72, 1.02, 1.24, 1.82)

# Quick Ratio data
quick_ratio_tech <- c(1.19, 1.34, 0.86, 1.48, 0.95, 1.51, 1.33, 1.03, 0.95, 1.77, 1.46, 0.71)
quick_ratio_speed <- c(1.71, 0.83, 1.51, 1.14, 0.99, 1.47, 0.77, 1.62, 1.48, 1.12, 0.89, 1.49)
quick_ratio_maclr <- c(1.90, 1.14, 1.13, 1.03, 1.90, 1.14, 1.13, 1.03, 1.03, 1.19, 1.83, 1.28)
quick_ratio_dain <- c(0.90, 2.12, 1.12, 1.26, 1.39, 1.43, 1.71, 0.79, 1.47, 0.82, 1.02, 1.50)

data_current <- data.frame(
  Quarter = rep(quarters, 4),
  Company = rep(c('Tech', 'Speed', 'Maclr', 'Dain'), each = length(quarters)),
  Value = c(current_ratio_tech, current_ratio_speed, current_ratio_maclr, current_ratio_dain),
  Measure = 'Current Ratio'
)

data_quick <- data.frame(
  Quarter = rep(quarters, 4),
  Company = rep(c('Tech', 'Speed', 'Maclr', 'Dain'), each = length(quarters)),
  Value = c(quick_ratio_tech, quick_ratio_speed, quick_ratio_maclr, quick_ratio_dain),
  Measure = 'Quick Ratio'
)

data <- bind_rows(data_current, data_quick)
# Filter data for Current Ratio
data_current_filtered <- data %>% filter(Measure == "Current Ratio")

# Plot Current Ratio
p_current <- ggplot(data_current_filtered, aes(x = Quarter, y = Value, fill = Company)) +
  geom_bar(stat = "identity", position = "dodge") +
  labs(title = "Current Ratio Comparison",
       x = "Year-Quarter",
       y = "Current Ratio",
       fill = "Company") +
  theme_minimal() +
  theme(axis.text.x = element_text(angle = 45, hjust = 1))

ggplotly(p_current)
# Filter data for Quick Ratio
data_quick_filtered <- data %>% filter(Measure == "Quick Ratio")

# Plot Quick Ratio
p_quick <- ggplot(data_quick_filtered, aes(x = Quarter, y = Value, fill = Company)) +
  geom_bar(stat = "identity", position = "dodge") +
  labs(title = "Quick Ratio Comparison",
       x = "Year-Quarter",
       y = "Quick Ratio",
       fill = "Company") +
  theme_minimal() +
  theme(axis.text.x = element_text(angle = 45, hjust = 1))

ggplotly(p_quick)

Conclusions:

Speed: Upon reviewing its data, it is evident that this company is the dominant player in the commercial sector. This dominance has allowed it to impose strict controls on Accounts Receivable, enabling the company to collect its funds promptly at the end of each quarter (“For example, if a customer purchases on account for $100,000 at the beginning of Q1 2021, they have until the end of Q1 2022 to settle the amount due”). This practice contributes to fluctuations in the Cash Conversion Cycle each year. Regarding the company’s debt, the Solvency Ratios indicate that the company leverages its market power and dominance to increase its borrowing capacity. This has significantly impacted the Net Profit Margin, as the higher level of debt has effectively reduced the company’s tax burden. In terms of profitability, the key indicators, ROE (Return on Equity) and OROA (Operating Return on Operating Assets), show positive performance for two reasons. Firstly, OROA remains strong because the company has maintained stable inventory turnover (meaning the period in which the inventory is quickly sold). Secondly, ROE has remained stable due to the improvement and consistency in the Dupont Analysis.

Maclr: Similar to the previous company, Maclr has comparable Margin rates, which is expected for all companies in this sector due to the above-average economic conditions and demand. In contrast to the previous company, Maclr does not impose any restrictions on Accounts Receivables. This is evident from the low Receivable Turnover over the years, which has significantly impacted the Cash Conversion Cycle by extending it. This extension is detrimental to any commercial company aiming to shorten the cycle for quick cash collection and inventory purchases. Consequently, this has also negatively affected the Activity Ratios, showing a decline over the years. Regarding Solvency, despite the cash flow issue, the company’s Net Profit Margin remains stable and does not decline. This is because the company heavily relies on external debt rather than operating cash flow and liquidity. Although this strategy has reduced taxes and increased net income, it places the company at high risk of bankruptcy if it fails to meet its debt obligations. This issue is further highlighted by the declining Profitability Ratios over the coming years, confirming the problem of reduced operating efficiency. Additionally, the declining inventory turnover over the years indicates that the company is struggling with its operational systems.

Dain: Regarding Margins, there are no issues, and the indicators are normal, similar to other companies in the same sector. However, Dain faces a problem similar to Maclr, as it does not appear to impose any restrictions on Accounts Receivables. The consequences of this have already been discussed in the case of Maclr. One significant difference is that Dain has a high inventory turnover, indicating it sells its products quickly, almost surpassing the dominant company, Speed. This efficiency has also helped Dain reduce its Cash Conversion Cycle. Additionally, Dain has managed to extend its Payable Turnover without adversely affecting its operations over the years. Therefore, its only issue lies in the Receivable Turnover. Regarding debt, Dain is similar to Maclr, except for the bankruptcy risk. Dain has sufficient liquidity from its operating activities to manage its debt obligations, especially short-term debts.

Tech: Unlike the other companies, Tech shows a decrease in Margin Ratios, which is unusual compared to the surrounding companies. Another issue is the lack of restrictions on Accounts Receivables, despite stable inventory turnover rates and an increase in Payable Turnover. The lack of controls on Accounts Receivables has significantly extended the Cash Conversion Cycle due to the decrease in Receivable Turnover Ratio. This situation poses a greater risk to the company, particularly if bad debt and the Allowance for Doubtful Accounts increase, which could severely weaken its liquidity ratios. Although the company does not rely on debt to the same extent as other companies, this will affect the stability of its operating systems. The ROA indicator is stable but tends to decline in the coming years. The OROA has decreased significantly due to the company’s deteriorating ability to increase sales and another reason that will be mentioned later. Now, addressing the crucial point, the company has declining Margins despite the surrounding companies not experiencing similar issues. If we look at the Gross Profit Margin, it is lower compared to other companies. This could be due to one of two reasons: either the company is not well-known and thus sells less, which is unlikely since the data shows that all four companies have nearly equal profits, or the second and more likely reason is that Tech’s product pricing is higher than that of other companies. Upon investigating, it was found that Tech has higher Costs of Goods Sold compared to the other companies. Regarding the EBIT Margin (Earnings Before Interest and Tax), it has been low over the years, also due to higher Operating Expenses than the other companies. Ultimately, this has resulted in a lower Net Profit Margin. Additionally, the lower debt levels compared to other companies have led to higher taxes for Tech compared to its peers.

->Now that we have reviewed each company, summarizing their advantages and disadvantages, let’s discuss the solutions that should be presented to Tech, in order to make it competitive with Speed and other Companies.

  1. Cost Analysis and Value Engineering: The first step Tech should take is to thoroughly examine its costs by implementing value engineering techniques. This involves identifying and eliminating all non-value-added activities to reduce overall costs. By doing so, the company can lower its product prices, thereby increasing its Gross Profit Margin and enhancing its inventory turnover.

  2. Implementing Appropriate Controls on Accounts Receivables: After reducing prices, the company should introduce suitable controls on Accounts Receivables that align with its market position. This strategy will help increase the Receivable Turnover, thereby shortening the Cash Conversion Cycle. Consequently, this will improve liquidity and boost operating activities.

  3. Optimizing Debt-to-Equity Ratio: Once the company stabilizes and its market value increases, it can leverage the third crucial factor: optimizing its Debt-to-Equity ratio. By aligning its debt levels with its financial position and liquidity at that time, the company can increase its Net Profit Margin and benefit from a reduction in taxes.

Implementing these strategies will enhance Tech’s competitiveness with Speed and other Companies and improve its overall financial performance.

Report

Click here to view the Companies Performance Report.